package com.aysafety.core.DBDriver.paradox;

import java.sql.*;
import java.util.*;

import org.apache.commons.lang3.StringUtils;

import com.aysafety.core.common.MD5;

public class ParadoxDriver {
    private static final String driver = "com.hxtt.sql.paradox.ParadoxDriver";
    private static Map<String, Connection> conn = new HashMap<String, Connection>();

    public ParadoxDriver() throws Exception {
        Class.forName(driver);
    }

    public static String connection(String jdbcPath, String locale, String charSet) throws Exception {
        String connKey = MD5.md5ToLower(jdbcPath);
        Properties properties = new Properties();
        properties.setProperty("charSet", charSet);
        properties.setProperty("locale", locale);
        if (!conn.containsKey(connKey)) {
            Connection tempConn = DriverManager.getConnection(jdbcPath, properties);
            conn.put(connKey, tempConn);
        }
        return connKey;
    }

    public static void close(String connKey) throws Exception {
        if (conn.containsKey(connKey)) {
            Connection tempConn = conn.get(connKey);
            conn.remove(connKey);
            if (null != tempConn) {
                tempConn.close();
            }
        }
    }

    public static Connection getConn(String connKey) throws Exception {
        Connection tempConn = null;
        if (conn.containsKey(connKey)) {
            tempConn = conn.get(connKey);
        } else {
            throw new Exception("使用方法时没有正确连接数据库");
        }
        return tempConn;
    }

    private static String generateSQL(String tableId, String condition, Map<String, String> structure) {
        String execSQL = "Select ";
        String columnStr = "";
        for (String key : structure.keySet()) {
            String columnValue = structure.get(key).replace("+", ",");
            if ("EntId".equals(structure.get(key))) {
                continue;
            }
            if (StringUtils.isBlank(columnValue)) {
                continue;
            }
            columnStr += columnValue + ",";
        }
        columnStr = columnStr.substring(0, columnStr.length() - 1);
        execSQL += columnStr + " from " + tableId;
        if (StringUtils.isNotBlank(condition)) {
            execSQL += " WHERE " + condition;
        }
        return execSQL;
    }

    private static Map<String, Object> transform(ResultSet rs, List<Map<String, Object>> structure) throws Exception {
        Map<String, Object> data = new HashMap<String, Object>();
        for (Map<String, Object> Column : structure) {
            String id = Column.get("id").toString();
            String type = Column.get("type").toString();
            if ("TIMESTAMP".equals(type)) {
                data.put(id, rs.getTimestamp(id));
            } else if ("BIGDECIMAL".equals(type)) {
                data.put(id, rs.getBigDecimal(id));
            } else if ("BOOLEAN".equals(type)) {
                data.put(id, rs.getBoolean(id));
            } else if ("DATE".equals(type)) {
                data.put(id, rs.getDate(id));
            } else if ("TIME".equals(type)) {
                data.put(id, rs.getTime(id));
            } else if ("DOUBLE".equals(type)) {
                data.put(id, rs.getDouble(id));
            } else if ("FLOAT".equals(type)) {
                data.put(id, rs.getFloat(id));
            } else if ("INT".equals(type)) {
                data.put(id, rs.getInt(id));
            } else if ("ARRAY".equals(type)) {
                data.put(id, rs.getArray(id));
            } else if ("VARCHAR".equals(type)) {
                String str = rs.getString(id);
                data.put(id, str);
            } else if ("CHAR".equals(type)) {
                String str = rs.getString(id);
                data.put(id, str);
            }

        }
        return data;
    }

    public static List<Map<String, Object>> allData(String tableId, String condition, String connKey,
        Map<String, String> structure) throws Exception {
        Connection tempConn = getConn(connKey);
        List<Map<String, Object>> allData = new ArrayList<Map<String, Object>>();
        PreparedStatement pstmt;
        String execSQL = generateSQL(tableId, condition, structure);
        pstmt = (PreparedStatement) tempConn.prepareStatement(execSQL);
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        List<Map<String, Object>> tableStructure = getStructure(data, connKey, structure);
        while (rs.next()) {
            Map<String, Object> temp = new HashMap<String, Object>();
            temp = transform(rs, tableStructure);
            allData.add(temp);
        }
        return allData;
    }

    public static Map<String, Object> firstData(String tableId, String condition, String connKey,
        Map<String, String> structure) throws Exception {
        Connection tempConn = getConn(connKey);
        Map<String, Object> firstData = new HashMap<String, Object>();
        PreparedStatement pstmt;
        String execSQL = generateSQL(tableId, condition, structure);
        pstmt = (PreparedStatement) tempConn.prepareStatement(execSQL);
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        List<Map<String, Object>> tableStructure = getStructure(data, connKey, structure);
        while (rs.next()) {
            if (rs.isFirst()) {
                firstData = transform(rs, tableStructure);
            }
        }
        return firstData;
    }

    public static Map<String, Object> lastData(String tableId, String condition, String connKey,
        Map<String, String> structure) throws Exception {
        Connection tempConn = getConn(connKey);
        Map<String, Object> lastData = new HashMap<String, Object>();
        PreparedStatement pstmt;
        String execSQL = generateSQL(tableId, condition, structure);
        pstmt = (PreparedStatement) tempConn.prepareStatement(execSQL);
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        List<Map<String, Object>> tableStructure = getStructure(data, connKey, structure);
        while (rs.next()) {
            if (rs.isLast()) {
                lastData = transform(rs, tableStructure);
            }
        }
        return lastData;
    }

    public static List<Map<String, Object>> getStructure(ResultSetMetaData data, String connKey,
        Map<String, String> structure) throws Exception {
        List<Map<String, Object>> tableStructure = new ArrayList<Map<String, Object>>();
        for (int i = 1; i <= data.getColumnCount(); i++) {
            Map<String, Object> structureData = new HashMap<String, Object>();
            structureData.put("id", data.getColumnName(i));
            structureData.put("type", data.getColumnTypeName(i));
            structureData.put("length", data.getColumnDisplaySize(i));
            tableStructure.add(structureData);
        }
        return tableStructure;
    }

    public static List<Map<String, Object>> getStructure(String tableId, String connKey, Map<String, String> structure)
        throws Exception {
        List<Map<String, Object>> tableStructure = new ArrayList<Map<String, Object>>();
        Connection tempConn = getConn(connKey);
        PreparedStatement pstmt;
        String execSQL = generateSQL(tableId, "", structure);
        pstmt = (PreparedStatement) tempConn.prepareStatement(execSQL);
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        for (int i = 1; i <= data.getColumnCount(); i++) {
            Map<String, Object> structureData = new HashMap<String, Object>();
            structureData.put("id", data.getColumnName(i));
            structureData.put("type", data.getColumnTypeName(i));
            structureData.put("length", data.getColumnDisplaySize(i));
            tableStructure.add(structureData);
        }
        return tableStructure;
    }
}
